Unlike most of the posts that I’ve put out recently which are more strategy based this post is a from-the-trenches-tip which I’ve recently come across that I thought I’d share. I’m going to talk all about using the Google Analytics API but I’ll show you how anyone can do it, you don’t need to be a developer! All you need is a Google Analyics account and Excel….
Introducing….. Excellent Analytics!
Excellent Analytics? Am I just bragging now? No, it’s actually the name of a tool – check it out here: http://excellentanalytics.com. It’s completely free and also truly excellent!
Excellent Analytics is an Excel plugin that you can download and integrate into most versions of Excel (EDIT – Greg pointed out in the comments that it only works for Excel 2007 – if you’re not compatible with Excel 2007 then check out the alternatives I link to further down). I’ve not done any testing but it certainly installed like a dream on Windows XP with Excel 2007. Once installed you get an extra tab within Excel that looks a bit like this:
So what does it do?
I’m glad you asked. Basically what it does is it provides a GUI (graphical user interface) for querying the Google Analytics API. You’ll need to give it your username and password and then you’ll be presented with a list of Metrics and Dimensions (just like building a custom report in Google Analytics) that you can choose to include or exclude. Excellent Analyics then pulls the data you’ve requested over the date range you’ve given it into the Excel sheet you’re working on. Nifty. But rather than bore you with hypothetical examples and generic screenshots let me walk you through a real-life example that I used it for only a few days ago.
Example – How To Get A Full List of Referring Pages
I was playing around with Google Analytics the other day for a new client and I was trying to pull off a list of their top referring pages. Now, you might think that was easy – after all Google Analytics has a Top Referrers report! Unfortunately, what this report doesn’t do for you is give you a list of referring pages, it just gives you the top referring sources. So the GA report will show me that SEOmoz.org sent more traffic than Twitter.com for example, but it won’t show me whether the homepage of SEOmoz sent more or less traffic than the Twitter homepage. This is the top referrers report in Google Analytics:
And playing around with custom reporting you can get this report of top referral paths:
As you can see though, we have difficulty matching up the referral path with the referral source. Neither report does exactly what I want. Enter on the scene Excellent Analyics. Let me show you how to build this report in Excellent Analyics.
Step 1 – open up a new Excellent Analytics report – tick “referral path” and “source” in Dimensions:
Step two – tick “visits” in Metrics (or anything else you want to report on, bounce rate, page views etc etc):
And then we have the final results (cleaned up a little):
Obviously this is only one very simple application of the tool – once you are querying the Google Analytics API the world is your oyster! If you come up with any more creative application please leave them in the comments.
It’s Limitations
Unfortunately the GA API only lets you return 5000 rows at a time so you can’t export pages and pages of data. That said, there is a filter function (which I didn’t really show above) which let’s you pre-filter the data. Using the filter you shouldn’t need to look at more than 5000 rows (how much data can you really take in anyway!?).
Other Google Analytics API Tools
For most of this post I’ve focused on Excellent Analytics but it’s worth pointing out that there are other tools to help you query the Google Analyics API. The GA blog actually posted a list of such tools which you can check out here in case Excellent Analytics isn’t the tool for you.
Improve Your Excel Skills
By the way, this post coincides nicely with Will’s next free SEO conference call he’s running on How to Be An Excel Ninja – Excel tips and tricks specifically designed for the kind of excel work you need as an SEO. Go over and sign up here 🙂
